package com.tim.dao.Impl;

import com.tim.dao.CarDao;
import com.tim.pojo.Car;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/28 20:23
 */
public class CarDaoImpl implements CarDao {
    @Override
    public int deleteCar(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from car where id = ?";
        Object[] args = {id};
        return qr.update(sql,args);
    }

    @Override
    public int addCar(Car car) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into car (id,photo,owner,color,carNum,detail,creatDate) values (?,?,?,?,?,?,?)";
        Object[] args = {car.getId(),car.getPhoto(),car.getOwner(),car.getColor(),car.getCarNum(),car.getDetail(),car.getCreatDate()};
        return qr.update(sql,args);
    }

    @Override
    public Car selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from car where id =?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<Car>(Car.class),args);
    }

    @Override
    public int updateCar(Car car) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "update car set photo=?,owner=?,color=?,carNum=?,detail=?,creatDate=? where id = ?";
        Object[] args={car.getPhoto(),car.getOwner(),car.getColor(),car.getCarNum(),car.getDetail(),car.getCreatDate(),car.getId()};
        return qr.update(sql,args);
    }

    @Override
    public List<Car> searchCar(String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from car where carNum like ?";
        Object[] args = {"%"+carNum+"%"};
        return qr.query(sql,new BeanListHandler<Car>(Car.class),args);
    }

    @Override
    public List<Car> limit(Integer currentPage, Integer pageSize, String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carNum == null){
            String sql = "select * from car limit ?,?";
            Object[] args={(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Car>(Car.class),args);
        }else{
            String sql = "select * from car where carNum like ? limit ?,?";
            Object[] args={"%"+carNum+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Car>(Car.class),args);
        }
    }

    @Override
    public int count(String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carNum == null){
            String sql = "select count(*) from car ";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from car where carNum like ?";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>(),"%"+carNum+"%").intValue();
        }
    }
}
